package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import control.ConnectionManage;


import db.parking.beans.Customer;


/**
 * AdminCustomer manages all requests From,To table Customer
 * @author Operations
 *
 */
public class AdminCustomer {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	/**
	 * Get row by customer id from DB table Customer
	 * @param cusId
	 * @return
	 * @throws SQLException
	 */
	public static Customer getRow(String cusId) throws SQLException {

		String sql = "SELECT * FROM Customer WHERE cusID = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, cusId);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Customer bean = new Customer();
				bean.setCusID(cusId);
				bean.setCusName(rs.getString("cusName"));
				bean.setAmount(rs.getFloat("amount"));
				bean.setNumOfCars(rs.getInt("numOfCars"));

	
			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 *  Get Customer bank balance by customer id from DB table Customer
	 * @param cusId
	 * @return
	 * @throws SQLException
	 */
	public static Customer getCustomerBalance(String cusId) throws SQLException {

		String sql = "SELECT amount FROM Customer WHERE cusID = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, cusId);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Customer bean = new Customer();
				bean.setCusID(cusId);
				bean.setAmount(rs.getFloat("amount"));
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * Get row by ID from DB table Customer
	 * @param cusId
	 * @return
	 * @throws SQLException
	 */
	public static Customer getOccasionalRow(String cusId) throws SQLException {

		String sql = "SELECT * FROM Customer WHERE cusID = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, cusId);

			rs = stmt.executeQuery();

			if (rs.next()) {
				Customer bean = new Customer();
				bean.setCusID(cusId);
				bean.setCusName(rs.getString("cusName"));
				bean.setAmount(rs.getFloat("amount"));
				bean.setNumOfCars(rs.getInt("numOfCars"));

			
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	/**
	 *  Insert a row by customer id to DB table Customer
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean insert(Customer bean) throws Exception {
    //complaintID is autoIncremented on Insert
		String sql = "INSERT into Customer (cusID , cusName , type) " +
				"VALUES (?, ?, ?)";
		ResultSet keys = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			
			stmt.setString(1, bean.getCusID());
			stmt.setString(2, bean.getCusName());
			stmt.executeUpdate();
		
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
			if(keys != null)
				keys.close();
		}
		return true;
		
	}
	//update complaint status
	/**
	 * Update Customer balance amount in DB table Customer by customer id.
	 * @param uid
	 * @param d
	 * @return
	 * @throws Exception
	 */
	public static boolean update(String uid,double d) throws Exception {

		String sql =
				"UPDATE Customer SET " +
				"amount = ? " +
				"WHERE cusID = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setDouble(1,d);
			stmt.setString(2,uid);
			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}

	
	/**
	 * Returns an array with ALL customers from DB table Customer
	 * @return
	 * @throws SQLException
	 */
	public static Customer[] getAllCustomers() throws SQLException {
		String sql = "SELECT * FROM customer";
		ResultSet rs = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
		
			
			rs = stmt.executeQuery();
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Customer bean[] = new Customer[totalRows];
			for (int i = 0; i < totalRows; i++)
				bean[i] = new Customer();
			int rowCount = 0;
			while (rs.next()) {
				bean[rowCount].setCusID(rs.getString("cusId"));
				bean[rowCount].setCusName(rs.getString("cusName"));
				bean[rowCount].setAmount(rs.getFloat("amount"));
				bean[rowCount].setNumOfCars(rs.getInt("numOfCars"));
				rowCount++;
			}
			return bean;
		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}
	}
}
